/**
 * LanguagesTable.java
 *
 * Copyright (C) 2009,  Tomislav Milkovic
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */    
package extjsdyntran.translation.store.schema;

import java.sql.*;
import java.util.ArrayList;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import extjsdyntran.translation.store.db.ATable;
import extjsdyntran.translation.store.db.DBaseFactory;
import extjsdyntran.translation.store.db.IRecord;
import extjsdyntran.translation.store.db.ITable;

    public class LanguagesTable extends ATable {

        private static LanguagesTable instance;
        private static final String tableName = "Languages";

        final private Logger logger = LoggerFactory.getLogger(TranslationsTable.class);
        
        private static final String createTable =
           "Create MEMORY Table " + tableName + " (" +
            "Obj OBJECT, " +                          
            "Id INTEGER IDENTITY PRIMARY KEY, " +     
            "Language VARCHAR(1024) NOT NULL, " +     
            "Is_Master BOOLEAN NOT NULL, " +          
            "UNIQUE (Id), " +
            "UNIQUE (Language)" +
            ");";
        

       public LanguagesTable() {
            instance = this;

            if(getCount() == -1) {
            	DBaseFactory.statementExecute(createTable);    
            	DBaseFactory.statementExecute("ALTER TABLE " + tableName + " ALTER COLUMN Id RESTART WITH 1;");                    
             }
            
            try {
            	Connection con = DBaseFactory.getConnection();
                prstInsert = con.prepareStatement("INSERT INTO " + tableName + " (Obj, Id, Language, Is_Master) VALUES (?, ?, ?, ?)");
                prstUpdate = con.prepareStatement("UPDATE " + tableName + " SET Is_Master =?, Language = ?, Obj = ? WHERE Id = ?;");
                prstDelete = con.prepareStatement("DELETE FROM " + tableName + " WHERE Id = ?;");
                prstGetById = con.prepareStatement("SELECT Obj FROM " + tableName + " WHERE Id = ?;");
                prstSelectAll = con.prepareStatement("SELECT Obj, Id FROM " + tableName + " ORDER BY Language;");
                prstDeleteAll = con.prepareStatement("DELETE FROM " + tableName + ";");  
                //prstGetByValue = con.prepareStatement("SELECT LIMIT 0 1 Obj, Id FROM " + tableName + " WHERE Language = ?;");
                prstGetByValue = con.prepareStatement("SELECT Obj, Id FROM " + tableName + " WHERE Language = ?;");
            }
            catch(SQLException e) {
                logger.error("Error preparing table {}.",tableName,e );
                throw new IllegalStateException("Error preparing table " + tableName + ".");
            }
            
        }

       public  IRecord sqlGetByValue(String value) {

           try {

        	   prstGetByValue.setString(1, value.toUpperCase());
               ArrayList<IRecord> recs = sqlSelectAll(prstGetByValue);
               if (recs.size() > 0) return recs.get(0);
               
           }
           catch(SQLException e) {
               logger.error("Error retrieving record by value {}", value, e);
           }
           return null;   	   
    	   
       }
        
       public  boolean sqlInsert(IRecord fn) {
            boolean status = false;
            try {
                prstInsert.setObject(1, fn, Types.JAVA_OBJECT);
                prstInsert.setNull(2, Types.INTEGER);
                prstInsert.setString(3, ((LanguagesPojo)fn).getLanguage().toUpperCase());
                prstInsert.setBoolean(4, ((LanguagesPojo)fn).isMaster());
                int nbChanged = DBaseFactory.statementExecute(prstInsert);
                DBaseFactory.statementExecute("COMMIT;");
                status = nbChanged > 0;
                fn.setId(DBaseFactory.getIdentity());
                
            }
            catch(SQLException e) {
            	logger.error("Error inserting record!",  e);
            }
            return status;
        }

       public boolean sqlUpdate(IRecord fn) {
            boolean status = false;
            try {
                prstUpdate.setBoolean(1, ((LanguagesPojo)fn).isMaster());
                prstUpdate.setString(2, ((LanguagesPojo)fn).getLanguage().toUpperCase());
                prstUpdate.setObject(3, fn, Types.JAVA_OBJECT);
                prstUpdate.setInt(4, fn.getId());
                int nbChanged = DBaseFactory.statementExecute(prstUpdate);
                status = nbChanged > 0;
            }
            catch(SQLException e) {
            	logger.error("Error updating record!",  e);
            }
            return status;
        }
        
        /** Returns the number of entries in the table */
        public int getCount() {
            return getCount(tableName);
        }

		public ITable getInstance() {
			return instance;
		}


    }